import sqlite3


class TimezoneDB:
    __slots__ = ['_connection']

    def __init__(self, name: str):
        self._connection = sqlite3.connect(name)

    def get_cities_timezones(self, request):
        cursor = self._connection.cursor()

        if request.language == 'ru':
            sql = "SELECT * FROM TIMEZONE_RU"
        else:
            sql = "SELECT * FROM TIMEZONE_EN"
        cursor.execute(sql)
        result = cursor.fetchall()
        return result

    def get_city_by_timezone(self, request):
        cursor = self._connection.cursor()

        timezone = request.timezone

        sql = "SELECT city_name FROM "
        if request.language == 'ru':
            sql += "TIMEZONE_RU "
        else:
            sql += "TIMEZONE_EN "
        sql += "WHERE UTC_timezone = \""
        if int(timezone) > 0:
            sql += f"+{timezone}\""
        else:
            sql += f"{timezone}\""

        cursor.execute(sql)
        result = cursor.fetchall()
        return result[0]
